package jsyf.oa.dao.system;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import com.mysql.cj.core.MysqlType;
import jsyf.oa.entity.system.CompanyEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;

import java.security.MessageDigest;

import org.springframework.stereotype.Repository;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import jsyf.com.dao.BaseJdbcDao;
import jsyf.oa.entity.system.UserEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by Administrator on 2018/1/4 0004.
 */
@Repository
public class UserDao extends BaseDao {

    @Autowired
    LogsDao logsDao;

    public UserDao() {
        super.iBaseEntity = new UserEntity();
    }

    public List<UserEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<UserEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            UserEntity entity = (UserEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, UserEntity.class);
    }

    @Override
    public Integer update(IBaseEntity entity) {
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.saveLogs(entity);
        return super.update(entity.getTableName(), entity.getJSONObject(), entity.getJSONObjectId());
    }
    public List<JSONObject> getViewListByPage(String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT *,  ");
        sql.append(" CASE WHEN jobState=-1 THEN 1");
        sql.append(" WHEN jobState=-2 ");
        sql.append(" THEN 2 ");
        sql.append(" WHEN jobState=1 ");
        sql.append(" THEN 3 ");
        sql.append(" ELSE 0 ");
        sql.append(" END AS jobStateSortNo ");
        sql.append(" FROM ");
        sql.append(iBaseEntity.getViewName());
        sql.append(" WHERE 1 = 1 ");
        sql.append(condition);
        sql.append(" ORDER BY jobStateSortNo,userSortNo");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);

        return jsonList;
    }
    @Override
    public Integer add(IBaseEntity entity) {
        entity.setId(super.generateKey());
        entity.setCreateTime(super.getCurrentTime());
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.addLogs(entity);
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }

    public Boolean isSuperUser(String userId) {
        String sql = "select count(1) from T_SYS_SUPER_USER WHERE USER_ID = ?";
        return (Integer.valueOf(super.queryForString(sql, new Object[]{userId}))) > 0;
    }

    public Boolean isTelUser(String tel) {
        String sql = "select count(1) from T_SYS_USER WHERE TEL = ? and JOB_STATE!=1";
        return (Integer.valueOf(super.queryForString(sql, new Object[]{tel}))) > 0;
    }

    public Integer delUser(UserEntity userEntity) {
        return this.update(userEntity);
    }

    public List<JSONObject> getViewByUserId(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT * FROM ");
        sql.append(" v_sys_user");
        sql.append(" WHERE 1 = 1 ");
        sql.append(condition);
        List<JSONObject> json = super.queryForJsonList(sql.toString(), params);
        return json;
    }

    public JSONObject getFirstParent(String userId) {

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT  \n");
        sql.append(" userTwo.id AS id, \n");
        sql.append(" userTwo.name AS name, \n");
        sql.append(" userTwo.roleName AS roleName, \n");
        sql.append(" userTwo.roleId AS roleId, \n");
        sql.append(" userTwo.departId AS departId, \n");
        sql.append(" userTwo.departName AS departName, \n");
        sql.append(" userTwo.companyName AS companyName, \n");
        sql.append(" userTwo.companyId AS companyId \n");
        sql.append(" FROM V_SYS_USER AS userOne \n");
        sql.append(" LEFT JOIN V_SYS_USER AS userTwo \n");
        sql.append(" ON userOne.parentId=userTwo.id \n");
        sql.append(" WHERE userOne.id=? \n");
        String[] params = {userId};

        return super.queryForJsonObject(sql.toString(), params);
    }

    public JSONObject getReceiverParent(String userId) {

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT  \n");
        sql.append(" userTwo.id AS userId, \n");
        sql.append(" userTwo.name AS userName, \n");
        sql.append(" userTwo.roleName AS roleName, \n");
        sql.append(" userTwo.roleId AS roleId, \n");
        sql.append(" userTwo.departId AS departId, \n");
        sql.append(" userTwo.departName AS departName, \n");
        sql.append(" userTwo.companyName AS companyName, \n");
        sql.append(" userTwo.companyId AS companyId \n");
        sql.append(" FROM V_SYS_USER AS userOne \n");
        sql.append(" LEFT JOIN V_SYS_USER AS userTwo \n");
        sql.append(" ON userOne.parentId=userTwo.id \n");
        sql.append(" WHERE userOne.id=? \n");
        String[] params = {userId};

        return super.queryForJsonObject(sql.toString(), params);
    }

    /**
     * 获取所有下级
     */
    public List<JSONObject> getSubordinateByParentId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\t`t_sys_user`.`ID` AS `id`,\n" +
                "\t`t_sys_user`.`NAME` AS `name`,\n" +
                "\t`t_sys_user`.`TEL` AS `tel`,\n" +
                "\t`t_sys_user`.`JOB_STATE` AS `jobState`,\n" +
                "\t`t_sys_role_name`.`NAME` AS `roleName`,\n" +
                "\t`t_sys_role_name`.`FRONT_FLAG` AS `frontFlag`,\n" +
                "\t`t_sys_role_name`.`AREA_TYPE` AS `areaType`,\n" +
                "\tt_sys_depart.PARENT_ID AS departParent\n" +
                "FROM\n" +
                "\t`t_sys_user`\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n");
        sql.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), new Object[]{});
        return jsonList;
    }

    /**
     * 获取抄送人
     */
    public List<JSONObject> getCopiers(String userId, String supportUserId, String parentUserId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_user_2_region.USER_ID AS userId,v_sys_user.`name` AS userName\n" +
                "FROM\n" +
                "\tt_sys_user_2_region LEFT JOIN v_sys_user ON t_sys_user_2_region.USER_ID=v_sys_user.id\n" +
                "  LEFT JOIN t_sys_role_name ON v_sys_user.roleNameId=t_sys_role_name.id\n" +
                "WHERE\n" +
                "\tREGION_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tREGION_ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user_2_region.USER_ID = \"" + userId + "\"\n" +
                "\t) \n" +
                "  AND t_sys_role_name.SUPPORT_FLAG=1\n" +
                "  AND v_sys_user.roleName in(\n" +
                "SELECT SUPPORT_ROLE_NAME from t_sys_support_role_name_2_role_name\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.roleName=t_sys_support_role_name_2_role_name.ROLE_NAME\n" +
                "WHERE v_sys_user.id=\"" + userId + "\"\n" +
                "AND t_sys_support_role_name_2_role_name.TYPE=0)\n" +
                "\tAND v_sys_user.id NOT in('" + userId + "','" + supportUserId + "','" + parentUserId + "','2017121618514C22B68296F31AF8DDE2') AND v_sys_user.jobState!=1\n" +
                "GROUP BY t_sys_user_2_region.USER_ID");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), new Object[]{});
        return jsonList;
    }

    /*
     * 获取区域支持查看的人
     */
    public List<JSONObject> getPeopleBySupportUserId(String condition, String userId, String todayTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "`t_sys_user`.`ID` AS `id`,\n" +
                "`t_sys_user`.`NAME` AS `name`,\n" +
                "`t_sys_role_name`.`NAME` AS `roleName`,\n" +
                "t_sys_company. NAME AS companyName,\n" +
                "t_sys_depart. NAME AS departName,\n" +
                "CASE \n" +
                "WHEN t_sys_depart.PARENT_ID='0'\n" +
                "THEN t_sys_depart.SORT_NO*100\n" +
                "ELSE(\n" +
                "SELECT t_sys_depart.SORT_NO FROM t_sys_depart AS departParent WHERE t_sys_depart.PARENT_ID=departParent.id)*100+t_sys_depart.SORT_NO+10\n" +
                "END AS departSortNo\n" +
                "FROM\n" +
                "\t`t_sys_user`\n" +
                "LEFT JOIN `t_sys_user_leave` ON `t_sys_user`.`ID` = `t_sys_user_leave`.`USER_ID`\n" +
                "LEFT JOIN `t_sys_role` ON `t_sys_user`.`ROLE_ID` = `t_sys_role`.`ID`\n" +
                "LEFT JOIN `t_sys_role_name` ON `t_sys_role`.`ROLE_NAME_ID` = `t_sys_role_name`.`ID`\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "WHERE\n" +
                "1 = 1\n" +
                "AND (\n" +
                "t_sys_user.JOB_STATE <> 1\n" +
                "OR (\n" +
                "t_sys_user.JOB_STATE = 1\n" +
                "AND LEFT (\n" +
                "t_sys_user_leave.LEAVE_DATE,\n" +
                "10\n" +
                ") = '" + todayTime + "'\n" +
                ")\n" +
                ")\n" +
                "AND (\n" +
                "t_sys_user.ID ='" + userId + "'\n");
        sql.append(condition);
        sql.append("OR t_sys_user.ID IN (\n" +
                "SELECT\n" +
                "USER_ID\n" +
                "FROM\n" +
                "t_sys_user_2_region\n" +
                "WHERE\n" +
                "REGION_ID IN (\n" +
                "SELECT\n" +
                "REGION_ID\n" +
                "FROM\n" +
                "t_sys_user_2_region\n" +
                "WHERE\n" +
                "USER_ID ='" + userId + "'\n" +
                ")\n" +
                ")\n" +
                "AND t_sys_role_name.FRONT_FLAG = 1\n" +
                ")\n" +
                "ORDER BY\n" +
                "t_sys_company.SORT_NO,\n" +
                "departSortNo,\n" +
                "t_sys_role.SORT_NO,\n" +
                "t_sys_user.SORT_NO");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), new Object[]{});
        return jsonList;
    }

    /**
     * 获取区域支持
     *
     * @param userId
     * @return
     */
    public JSONObject getSupportByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT d.id,d.name from ( ");
        sql.append("SELECT * from t_sys_user_2_region where region_id in( ");
        sql.append("SELECT region_id FROM t_sys_user_2_region as a ");
        sql.append("left join t_sys_region as b on a.region_id=b.id ");
        sql.append("where a.user_id='" + userId + "' ");
        sql.append(")) as c ");
        sql.append("left join v_sys_user as d on c.user_id=d.id where d.roleName='区域支持' AND d.jobState<>1 limit 1 ");
        return queryForJsonObject(sql.toString());
    }


    public List<JSONObject> getParentList(String userId, List<JSONObject> parentList) {
        JSONObject parentObj = this.getFirstParent(userId);

        if (parentObj != null) {
            for (int i = 0; i < parentList.size(); i++) {
                if (parentObj.getString("id").equals(parentList.get(i).getString("id"))) {
                    return parentList;
                }
            }
            parentList.add(parentObj);
            this.getParentList(parentObj.getString("id"), parentList);
        } else {
            return parentList;
        }
        return parentList;
    }

    public Boolean sort(String userIds) {
        List<String> list = BaseJdbcDao.stringToList(userIds);
        for (int i = 0; i < list.size(); i++) {
            JSONObject obj = new JSONObject();
            obj.put("ID", list.get(i));
            obj.put("SORT_NO", i);

            JSONObject objId = new JSONObject();
            objId.put("ID", list.get(i));
            super.update("T_SYS_USER", obj, objId);
        }
        return true;
    }

    public List<JSONObject> getSupportersByUserId(String userId) {
        return this.getJSONListByCondition(" AND ID in(\n" +
                "SELECT USER_ID FROM T_SYS_USER_2_REGION WHERE REGION_ID IN(\n" +
                "SELECT REGION_ID FROM T_SYS_USER_2_REGION WHERE USER_ID = ?\n" +
                ")\n" +
                ") AND ROLE_ID IN(\n" +
                "SELECT ID FROM V_SYS_ROLE WHERE frontFlag = 0 \n" +
                ")", new Object[]{userId});
    }

//    public Integer add(UserEntity entity) {
//        entity.setId(super.generateKey());
//        entity.setCreateTime(super.getCurrentTime());
//        entity.setUpdateTime(super.getCurrentTime());
//        return super.add(entity);
//    }

    public Date nowDate() {
        return this.getJdbcTemplate().queryForObject("select curdate();", Date.class);
    }

    public JSONObject getViewJSONByName(String name) {
        List<JSONObject> jsonList = this.getViewJSONListByCondition(" AND name = ?", new Object[]{name});
        if (jsonList != null && jsonList.size() > 0) {
            return jsonList.get(0);
        } else {
            return null;
        }
    }

    public List<JSONObject> getGroupFrontLine() {

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT c1.id AS companyId, c1.`NAME` AS companyName, v_sys_user.id, v_sys_user.`name`, ");
        sb.append(" (SELECT COUNT(*) from v_sys_user WHERE v_sys_user.companyId=c1.ID AND (v_sys_user.roleName='区域经理' OR v_sys_user.roleName='大区经理') and v_sys_user.delFlag=0) AS regionNumber ");
        sb.append(" FROM t_sys_company as c1 ");
        sb.append(" LEFT JOIN v_sys_user ON c1.ID=v_sys_user.companyId ");
        sb.append(" where 1=1 ");
        sb.append(" AND v_sys_user.roleName='总经理' ");
        sb.append(" AND c1.PARENT_ID='201712161613432EAF27DBCCB7D0E6CA' ");
        sb.append(" AND c1.DEL_FLAG = 0 ");

        List<JSONObject> jsonList = super.queryForJsonList(sb.toString(), new Object[]{});
        return jsonList;
    }

    /**
     * 获取微信企业号 联系人
     *
     * @param userId
     * @return
     */
    public String getWeChatMsgIds(String userId) {
        String toUserIds = "";
        List<JSONObject> jsonList = getViewJSONListByCondition(" AND main = 1 AND id =?", new String[]{userId});
        if (jsonList != null && jsonList.size() > 0) {
            //查询上级
            List<JSONObject> parentList = new ArrayList<>();
            parentList.add(getFirstParent(userId));
//            parentList = addNoRepeat(userId, parentList, getParentList(userId, jsonList));
            if ("大区经理".equals(jsonList.get(0).getString("roleName")) || "区域经理".equals(jsonList.get(0).getString("roleName"))) {
                //区域支持
                JSONObject supportObj = getSupportByUserId(userId);
                if (supportObj != null) {
                    parentList.add(supportObj);
                }

                //parentList = addNoRepeat(userId, parentList, getParentList(supportObj.getString("id"), parentList));
            }
            if (parentList != null && parentList.size() > 0) {
                for (JSONObject jo : parentList) {
                    if (!jo.getString("id").replace(" ", "").equals("") && jo.getString("id") != null && !jo.getString("id").isEmpty()) {
                        toUserIds += jo.getString("id") + "|";
                    }
                }
                if (toUserIds != null && !toUserIds.isEmpty()) {
                    toUserIds = toUserIds.substring(0, toUserIds.length() - 1);
                }

            }
        }
        return toUserIds;
    }

    public UserEntity getById(String id) {
        return (UserEntity) super.getIBaseEntityById(id);
    }

    public void getChildrenIds(String userId, List<String> listUserIds) {
        UserEntity entity = this.getById(userId);
        List<JSONObject> children = this.getJSONListByCondition(" AND PARENT_ID = ? AND JOB_STATE!=1", new Object[]{entity.getId()});
        if (children == null || children.size() == 0) {

        } else {
            for (int i = 0; i < children.size(); i++) {
                String childrenUserId = children.get(i).getString("id");
                listUserIds.add(childrenUserId);
                getChildrenIds(childrenUserId, listUserIds);
            }
        }
    }

    /**
     * 获取用户主职位信息
     *
     * @param userId
     * @return 江苏瀛沣-研发部-郭琦
     */
    public String getUserMainInfo(String userId) {
        List<JSONObject> jsonUser = getViewJSONListByCondition(" AND main = 1 AND id =?", new String[]{userId});
        if (jsonUser != null && jsonUser.size() > 0) {
            return jsonUser.get(0).getString("companyName") + "-" + jsonUser.get(0).getString("roleName") + "-" + jsonUser.get(0).getString("name");
        }
        return "未知";
    }

    private List<JSONObject> addNoRepeat(String selfUserId, List<JSONObject> originList, List<JSONObject> newList) {
        String ids = selfUserId;
        for (JSONObject jo : originList) {
            ids += jo.getString("id") + "|";
        }
        for (JSONObject newJo : newList) {
            if (!ids.contains(newJo.getString("id"))) {
                originList.add(newJo);
            }
        }
        return originList;
    }
    /*
     * JAVA调用存储过程
     * @param uname
     * @return

    public List<IBaseEntity> javaInvokePro(String uname) throws SQLException {
       List<IBaseEntity> list = new ArrayList<>();
       CallableStatement cs = this.getJdbcTemplate().getDataSource().getConnection().prepareCall(
               "{call pro_user(?)}"
       );
       cs.setString(1,uname);
       ResultSet result = cs.executeQuery();
       while (result.next()){
           IBaseEntity entity = new UserEntity();
           entity.setId(result.getString("ID"));
           entity.setCreateTime(result.getDate("CREATE_TIME"));
           list.add(entity);
       }

        return list;
    }*/

    /**
     * java调用存储过程实现INSERT操作
     *
     * @param
     * @return
     *//*
    public Boolean  insertProcedure(JSONObject object){
        String regionId = object.getString("regionId");
        List<String> provinceIds = (List<String>)object.get("list");
        try {
            CallableStatement cs = this.getJdbcTemplate().
                    getDataSource().getConnection().prepareCall("{call pro_insertRegion2Province(?,?,?)}");
            int row = 0;
           for(int i=0; i<provinceIds.size(); i++){
               cs.setString(1,this.getGenerateKey(new Date()));
               cs.setString(2,regionId);
               cs.setString(3,provinceIds.get(i));
              row +=  cs.executeUpdate();
           }
           return row == provinceIds.size() ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
       return false;
    }*/
    public List<JSONObject> getContacts(String condition) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" u.id,u.companyName,u.name,u.departName,u.tel,u.jobState,u.roleName,u.email,u.carCard  \n");
        sb.append(" FROM v_sys_user  AS u  where 1 = 1 \n");
        sb.append(condition);
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> AllUser(String conditon, String beginTime, String endTime) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        sql.append(" SELECT DISTINCT(v_sys_user.id) AS id,v_sys_user.`name` as name");
        sql.append(" FROM v_sys_user LEFT JOIN t_sys_leave ON v_sys_user.id=t_sys_leave.USER_ID");
        sql.append(" WHERE delFlag != 1 AND jobState != 1 AND v_sys_user.id not in(");
        sql.append(" SELECT DISTINCT(USER_ID) FROM t_sys_leave WHERE ? BETWEEN t_sys_leave.BEGIN_TIME AND t_sys_leave.END_TIME)");
        sqlArgs.add(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
        sql.append(" AND v_sys_user.roleName not in(\"门卫\",\"保洁\",\"厨师\",\"帮厨\",\"总裁\")");
        sql.append(" AND v_sys_user.id NOT IN (SELECT DISTINCT (t_sys_sign_in.USER_ID) FROM t_sys_sign_in");
        sql.append(" WHERE t_sys_sign_in.CREATE_TIME BETWEEN ? AND ?)");
        sqlArgs.add(beginTime);
        sqlArgs.add(endTime);
        sql.append(conditon);
        sql.append(" AND companyName!=\"内蒙辽鞍\" AND companyName!=\"山西沃源\"");
        sql.append(" GROUP BY v_sys_user.id");
        return super.queryForJsonList(sql.toString(), sqlArgs.toArray());
    }

    public List<JSONObject> getALLFront() {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT DISTINCT(v_sys_user.id) AS id");
        sql.append(" FROM v_sys_user");
        sql.append(" WHERE delFlag != 1 AND jobState != 1 ");
        sql.append(" AND companyName!=\"内蒙辽鞍\" AND companyName!=\"山西沃源\"");
        sql.append(" AND frontFlag=1");
        sql.append(" GROUP BY v_sys_user.id");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getHRUser(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" id,companyId,companyName");
        sql.append(" FROM");
        sql.append(" v_sys_user");
        sql.append(" WHERE");
        sql.append(" roleName IN (\"人事专员\",\"人事经理\") AND jobState != 1");
        sql.append(" AND companyName NOT IN (\"山西沃源\",\"内蒙辽鞍\") ");
        sql.append(condition);
        sql.append(" ORDER BY companyName");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getCompanyUsers(String companyId, String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" id,name FROM v_sys_user");
        sql.append(" WHERE companyId=? AND jobState != 1 ");
        sql.append(" AND id not in (\"2017121618514C22B68296F31AF8DDE2\",?)");
        sql.append(" GROUP BY id");
        return super.queryForJsonList(sql.toString(), new Object[]{companyId, userId});
    }

    public List<JSONObject> getBirthdayUsers(String date, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" id,companyId,name");
        sql.append(" FROM");
        sql.append(" v_sys_user");
        sql.append(" WHERE");
        sql.append(" right(CAST(SUBSTRING(card, 7, 8) AS date),5) =? AND jobState != 1");
        sql.append(" AND id!=\"2017121618514C22B68296F31AF8DDE2\" ");
        if (StringUtils.isNotEmpty(companyId)) {
            sql.append(" AND companyId='" + companyId + "'");
            sql.append(" GROUP BY id");
        } else {
            sql.append(" GROUP BY companyId");
        }

        return super.queryForJsonList(sql.toString(), new Object[]{date});
    }

    public List<JSONObject> RevertPinyinUser() {
        StringBuffer sql = new StringBuffer();
        sql.append(" select ID as id,`NAME` as name,SPELL as spell from t_sys_user ");
        return super.queryForJsonList(sql.toString());
    }

    /**
     * 生成md5
     *
     * @param message
     * @return
     */
    public String getMD5(String message) {
        String md5str = "";
        try {
            // 1 创建一个提供信息摘要算法的对象，初始化为md5算法对象
            MessageDigest md = MessageDigest.getInstance("MD5");
            // 2 将消息变成byte数组
            byte[] input = message.getBytes();
            // 3 计算后获得字节数组,这就是那128位了
            byte[] buff = md.digest(input);
            // 4 把数组每一字节（一个字节占八位）换成16进制连成md5字符串
            md5str = bytesToHex(buff);
            //md5str.substring(8, 24);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return md5str.substring(8, 24);
    }

    /**
     * 二进制转十六进制
     *
     * @param bytes
     * @return
     */
    public String bytesToHex(byte[] bytes) {
        StringBuffer md5str = new StringBuffer();
        // 把数组每一字节换成16进制连成md5字符串
        int digital;
        for (int i = 0; i < bytes.length; i++) {
            digital = bytes[i];

            if (digital < 0) {
                digital += 256;
            }
            if (digital < 16) {
                md5str.append("0");
            }
            md5str.append(Integer.toHexString(digital));
        }
        return md5str.toString().toUpperCase();
    }

    /**
     * MD5Reverse
     *
     * @return
     */
    public JSONObject MD5Crack(JSONObject object) {
        StringBuilder sb = new StringBuilder(object.getString("MD5"));
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("MD5", sb.reverse().toString());
        return jsonObject;
    }

    public StringBuilder ForgetPwd() {
        StringBuilder Random = new StringBuilder();
        for (int i = 0; i < 4; i++) {
            Random.append((int) (Math.random() * 10));
        }
        return Random;
    }

    /**
     * 支持岗位，根据区域获取支持的数据
     *
     * @return
     */
    public List<JSONObject> getSupportUserList(String userId, String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t_sys_user.ID as userId,t_sys_user.`NAME` as userName\n" +
                "FROM t_sys_user WHERE t_sys_user.ID in(\n" +
                " SELECT sysUser.userId FROM\n" +
                "(\n" +
                "SELECT DISTINCT(USER_ID)  as userId\n" +
                " from t_sys_user_2_region WHERE REGION_ID in(\n" +
                "SELECT REGION_ID from t_sys_user_2_region WHERE USER_ID = '" + userId + "'\n" +
                ") AND USER_ID in (\n" +
                "select USER_ID from t_sys_user where JOB_STATE <> 1\n" +
                ")) as sysUser\n" +
                "LEFT JOIN v_sys_part_time_for_report ON sysUser.userId=v_sys_part_time_for_report.userId\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "WHERE 1=1 AND v_sys_part_time_for_report.jobState!=1\n" +
                "AND t_sys_role_name.`NAME` IN \n");
        if (condition == null) {
            sql.append("(SELECT t_sys_support_role_name_2_role_name.ROLE_NAME as roleName\n" +
                    " from t_sys_support_role_name_2_role_name WHERE SUPPORT_ROLE_NAME in(\n");
        } else {
            sql.append("(SELECT t_sys_support_role_name_2_role_name.SUPPORT_ROLE_NAME as roleName\n" +
                    " from t_sys_support_role_name_2_role_name WHERE ROLE_NAME in(\n");
        }
        sql.append("SELECT t_sys_role_name.`NAME` as roleName FROM\n" +
                "(\n" +
                "SELECT ROLE_ID from t_sys_user WHERE ID = '" + userId + "'\n" +
                "UNION ALL\n" +
                "SELECT ROLE_ID from t_sys_user_2_role WHERE USER_ID = '" + userId + "') AS role\n" +
                "LEFT JOIN t_sys_role on role.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                ")AND t_sys_support_role_name_2_role_name.TYPE=0)\n");
        if (condition != null) {
            sql.append(condition);
        }
        sql.append("\nGROUP BY sysUser.userId)\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getDepartByUserId(String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_depart.ID AS id,\n" +
                "\tt_sys_depart.`NAME` AS name,\n" +
                "\tt_sys_depart.COMPANY_ID AS companyId,\n" +
                "\tt_sys_depart.PARENT_ID AS parentId,\n" +
                "\tt_sys_depart.sort_no AS sortNo\n" +
                "FROM\n" +
                "\tt_sys_depart\n" +
                "LEFT JOIN t_sys_role ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user.ID IN (" + userIds + ") " +
                "AND t_sys_role.DEL_FLAG=0 AND t_sys_depart.DEL_FLAG=0 AND t_sys_user.JOB_STATE!=1 GROUP BY t_sys_depart.ID ORDER BY t_sys_depart.SORT_NO,t_sys_role.SORT_NO,t_sys_user.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getDepartByUserIdContainLeave(String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_depart.ID AS id,\n" +
                "\tt_sys_depart.`NAME` AS name,\n" +
                "\tt_sys_depart.COMPANY_ID AS companyId,\n" +
                "\tt_sys_depart.PARENT_ID AS parentId,\n" +
                "\tt_sys_depart.sort_no AS sortNo\n" +
                "FROM\n" +
                "\tt_sys_depart\n" +
                "LEFT JOIN t_sys_role ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_depart.NAME <> '销售部'\n" +
                "AND t_sys_user.ID IN (" + userIds + ") " +
                "AND t_sys_role.DEL_FLAG=0 AND t_sys_depart.DEL_FLAG=0  GROUP BY t_sys_depart.ID ORDER BY t_sys_depart.SORT_NO,t_sys_role.SORT_NO,t_sys_user.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getCompanyByUserId(String userIds) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_company.ID AS id,\n" +
                "\tt_sys_company.`NAME` AS `name`,\n" +
                "\tt_sys_company.PARENT_ID AS parentId\n" +
                "FROM\n" +
                "\tt_sys_company\n" +
                "LEFT JOIN t_sys_depart ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_role.ID = t_sys_user.ROLE_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user.ID IN (" + userIds + ")\n" +
                "AND t_sys_company.DEL_FLAG=0 AND t_sys_role.DEL_FLAG=0 AND t_sys_depart.DEL_FLAG=0 AND t_sys_user.JOB_STATE!=1\n" +
                "GROUP BY t_sys_company.ID\n" +
                "ORDER BY t_sys_company.SORT_NO,t_sys_depart.SORT_NO,t_sys_role.SORT_NO,t_sys_user.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }

    /**
     * mysql自定义函数递归查询下级人员(不包含自身)
     * 返回值为 -> ""(空字符串)、1,2,3(用逗号隔开的id)
     *
     * @return
     */
    public List<JSONObject> getChilderByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT getChilderByUserId('" + userId + "') as ids;\n");
        return super.queryForJsonList(sql.toString());
        //自定义函数查询人员下级
        /*DROP FUNCTION IF EXISTS getChilderByUserId;
        CREATE FUNCTION getChilderByUserId(userId VARCHAR(32))
        RETURNS text
        BEGIN
        DECLARE sTemp text;
        DECLARE sTempChd text;
        -- SET GLOBAL group_concat_max_len = 1844674407370955161;
        SET SESSION group_concat_max_len = 1844674407370955161;
        SET sTempChd = CAST(userId AS CHAR);
        SET sTemp=NULL;
        WHILE sTempChd IS NOT NULL DO
        IF sTemp IS  NULL THEN  SET sTemp= sTempChd;
        ELSE SET sTemp= CONCAT(sTemp,',',sTempChd);
        END IF;
        SELECT GROUP_CONCAT(t_sys_user.ID) INTO sTempChd FROM t_sys_user WHERE FIND_IN_SET(t_sys_user.PARENT_ID,sTempChd)>0 AND t_sys_user.JOB_STATE!=1;
        END WHILE;
        IF locate(CONCAT(userId,','),sTemp)>0
        THEN
        SET sTemp=REPLACE(sTemp,CONCAT(userId,','),'');
        ELSE
        SET sTemp=REPLACE(sTemp,CAST(userId AS CHAR),'');
        END IF;
        RETURN sTemp;
        END;*/
    }

    /**
     * mysql自定义函数递归查询下级人员(不包含自身)
     * 返回值为 -> ""(空字符串)、1,2,3(用逗号隔开的id)
     * 包括离职人员
     *
     * @return
     */
    public List<JSONObject> getChilderByUserIdContainLeave(String userId, String createUserId, String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "v_sys_user.id AS userId,\n" +
                "v_sys_user.`name` AS userName\n" +
                "FROM \n" +
                "v_sys_user\n" +
                "WHERE v_sys_user.departName in(\n" +
                "SELECT \n" +
                "t_sys_region.`NAME` AS regionName\n" +
                " FROM t_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "WHERE t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.PARENT_ID=0\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                ")\n" +
                "AND v_sys_user.companyId in(" + companyId + ")\n" +
                "AND ( v_sys_user.roleName='区域经理' OR v_sys_user.roleName='大区经理')\n" +
                "AND v_sys_user.id in(" + createUserId + ")\n" +
                "GROUP BY v_sys_user.id\n" +
                "ORDER BY v_sys_user.companySortNo,v_sys_user.departSortNo,v_sys_user.roleSortNo,v_sys_user.jobState");
        return super.queryForJsonList(sql.toString());

    }

    /**
     * 查询客户表离职的创建者
     *
     * @return
     */
    public List<JSONObject> getCreateUserId() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                " t_wrt_customer.CREATE_USER_ID AS id\n" +
                "FROM t_wrt_customer\n" +
                " WHERE  ( t_wrt_customer.CREATE_USER_ID IS NOT NULL  OR  t_wrt_customer.CREATE_USER_ID='')\n" +
                "AND t_wrt_customer.NAME IS NOT NULL\n" +
                "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                "GROUP BY t_wrt_customer.CREATE_USER_ID");
        return super.queryForJsonList(sql.toString());
    }


    /**
     * mysql自定义函数递归查询上级人员(不包含自身)
     * 返回值为 -> ""(空字符串)、1,2,3(用逗号隔开的id)
     *
     * @return
     */
    public List<JSONObject> getParentByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT getParentByUserId('" + userId + "') as ids;\n");
        return super.queryForJsonList(sql.toString());
        //自定义函数查询人员上级
        /*DROP FUNCTION IF EXISTS getParentByUserId;
        CREATE FUNCTION getParentByUserId(userId VARCHAR(32))
        RETURNS text
        BEGIN
        DECLARE sTemp text;
        DECLARE sTempChd text;
        -- SET GLOBAL group_concat_max_len = 1844674407370955161;
        SET SESSION group_concat_max_len = 1844674407370955161;
        SET sTemp=NULL;
        SET sTempChd = CAST(userId AS CHAR);
        IF sTemp IS  NULL THEN  SET sTemp= sTempChd;
        ELSE SET sTemp= CONCAT(sTemp,',',sTempChd);
        END IF;
        SELECT t_sys_user.PARENT_ID INTO sTempChd FROM t_sys_user WHERE id = sTempChd AND t_sys_user.JOB_STATE!=1;
        WHILE sTempChd <> 0
        DO
        SET sTemp = CONCAT(sTemp,',',sTempChd);
        SELECT t_sys_user.PARENT_ID INTO sTempChd FROM t_sys_user WHERE id = sTempChd AND t_sys_user.JOB_STATE!=1;
        END WHILE;
        IF locate(CONCAT(userId,','),sTemp)>0
        THEN
        SET sTemp=REPLACE(sTemp,CONCAT(userId,','),'');
        ELSE
        SET sTemp=REPLACE(sTemp,CAST(userId AS CHAR),'');
        END IF;
        RETURN sTemp;
        END;*/
    }

    //通过userId和roleName获取抄送人
    public List<JSONObject> getUserByUserIdAndRoleName(String userIds, String roleName, String userId, String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT  v_sys_part_time_for_report.userId,t_sys_role_name.`NAME` as roleName,\n" +
                "v_sys_part_time_for_report.userName as  copierName\n" +
                "FROM v_sys_part_time_for_report LEFT JOIN t_sys_role_name\n" +
                "ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "WHERE t_sys_role_name.DEL_FLAG=0\n" +
                "AND v_sys_part_time_for_report.jobState!=1\n");
        if (!StringUtils.isBlank(roleName)) {
            sql.append("AND t_sys_role_name.`NAME` = '" + roleName + "'\n");
        }
        sql.append("AND v_sys_part_time_for_report.userId in (" + userIds + ")\n" +
                "AND v_sys_part_time_for_report.userId !='" + userId + "'\n");
        sql.append(condition + "\n");
        sql.append("group by userId\n" +
                "ORDER BY roleSortNo\n");
        return super.queryForJsonList(sql.toString());
    }

    //通过userId和roleName获取审核人
    public List<JSONObject> getUserByUserIdAndRoleName(String userIds, String roleName, String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT v_sys_part_time_for_report.userId,t_sys_role_name.`NAME` as roleName,\n" +
                "v_sys_part_time_for_report.userName as auditorName\n" +
                "FROM v_sys_part_time_for_report LEFT JOIN t_sys_role_name\n" +
                "ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "WHERE t_sys_role_name.DEL_FLAG=0\n" +
                "AND v_sys_part_time_for_report.jobState!=1\n" +
                "AND t_sys_role_name.`NAME` = '" + roleName + "'\n" +
                "AND v_sys_part_time_for_report.userId in (" + userIds + ")\n" +
                "AND v_sys_part_time_for_report.userId !='" + userId + "'\n" +
                "ORDER BY roleSortNo\n");
        return super.queryForJsonList(sql.toString());
    }

    //通过userId和roleName获取人
    public List<JSONObject> getUserByUserIdAndRoleName(String userId, String roleName) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tv_sys_part_time_for_report.userId AS userId,\n" +
                "\tv_sys_part_time_for_report.userName AS userName\n" +
                "FROM\n" +
                "\tv_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = v_sys_part_time_for_report.roleNameId\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND v_sys_part_time_for_report.userId IN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user_2_region.USER_ID AS userId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tWHERE\n" +
                "\t\tt_sys_user_2_region.REGION_ID IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_user_2_region.REGION_ID AS regionId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tt_sys_user_2_region.USER_ID = ? \n" +
                "\t\t)\n" +
                ")\n" +
                "AND t_sys_role_name.`NAME` = ? \n" +
                "AND v_sys_part_time_for_report.jobState!=1\n" +
                "GROUP BY\n" +
                "\tv_sys_part_time_for_report.userId\n");
        return super.queryForJsonList(sql.toString(), new Object[]{userId, roleName});
    }

    //通过companyId和roleName获取人
    public List<JSONObject> getUserByCompanyIdAndRoleName(String userId, String roleName, String companyId, String parentIds) {
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<String>();
        sql.append("SELECT\n" +
                "\tv_sys_part_time_for_report.userId AS userId,\n" +
                "\tv_sys_part_time_for_report.userName AS userName\n" +
                "FROM\n" +
                "\tv_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = v_sys_part_time_for_report.roleNameId\n" +
                "WHERE\n" +
                "\t1 = 1\n");
        if (roleName.indexOf("集团") < 0) {
            sql.append("AND v_sys_part_time_for_report.companyId= ? \n");
            params.add(companyId);
        }
        sql.append("AND t_sys_role_name.`NAME` = ? \n" +
                "AND v_sys_part_time_for_report.jobState!=1\n" +
                "AND v_sys_part_time_for_report.userId!=? \n");
        if (roleName.indexOf("财务") < 0 && roleName.indexOf("服务") < 0) {
            sql.append("AND v_sys_part_time_for_report.userId in (" + parentIds + ")\n");
        }
        sql.append("GROUP BY\n" +
                "\tv_sys_part_time_for_report.userId\n");
        params.add(roleName);
        params.add(userId);
        return super.queryForJsonList(sql.toString(), params.toArray());
    }

    //判断离职人员是否需要给应收专员发消息
    public List<JSONObject> getDataLength(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT  * FROM t_con_main WHERE");
        sql.append(" REGION_MANAGER_ID = '" + userId + "'");
        sql.append(" OR BIG_REGION_MANAGER_ID  = '" + userId + "'");
        sql.append(" OR REGIONAL_SUPPORT_ID = '" + userId + "'");
        sql.append(" AND AUDIT_FLAG=1 AND HAVE_SETTLE=0");
        return super.queryForJsonList(sql.toString());
    }

    //通过ID获取离职人员
    public List<JSONObject> getResignationByUserId(String companyIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT id as userId\n" +
                "FROM v_sys_user\n" +
                "WHERE 1=1\n" +
                "AND v_sys_user.jobState=1\n" +
                "AND v_sys_user.companyId in (" + companyIds + ")");
        return super.queryForJsonList(sql.toString());
    }
}
